﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;

namespace Intel.Server.WCFLibrary
{
    public enum DataType { Mouse, Key,SysInfo }
    interface IDatabase
    {
        void LoadDataInfile(string FileName, DataType type, int id);
        DateTime GetUserLastUploadTime(int id, DataType type);
    }
    public class MysqlDb : IValidator, IDatabase
    {
        public MysqlDb()
        {
            MySqlConnectionStringBuilder ConBuilder = new MySqlConnectionStringBuilder();
            //ConBuilder.Server="localhost";
            //ConBuilder.Database="intelpchms";
            //ConBuilder.UserID="root";
            //ConBuilder.Password="badtoy007";
            //Connection = new MySqlConnection(ConBuilder.GetConnectionString(true));
            //System.Windows.Forms.MessageBox.Show(ConBuilder.GetConnectionString(true));
           // var s = new Properties.Settings();
            //System.Configuration.ConfigurationSettings.AppSettings[""]
            string Con = System.Configuration.ConfigurationSettings.AppSettings["connstring"];
            Console.WriteLine(Con);
            Connection = new MySqlConnection(Con);
            Connection.Open();

        }

        public int Validate(string Name, string Password)
        {
            string CmdStr = string.Format("select userid from usertable where username='{0}' and password ='{1}'", Name, Password);
            MySqlCommand Cmd = new MySqlCommand(CmdStr, Connection);
            var Result = Cmd.ExecuteScalar();
            if (Result == null)
                return -1;
            else
            {
                int r = int.Parse(Result.ToString());
                return r;
            }
             

        }

        public void LoadDataInfile(string FileName,DataType type, int id)
        {
            string CmdStr;
            //System.Windows.Forms.MessageBox.Show(FileName);
            switch (type)
            {
                case DataType.Mouse:
                    {
                        CmdStr = string.Format(@"LOAD DATA INFILE '{0}' INTO TABLE mousetable FIELDS TERMINATED BY '\t' (xpos,ypos,processname,dtstamp) set userid={1}",
                                                 FileName, id);
                        break;
                    }
                case DataType.Key:
                    {
                       CmdStr = string.Format( @"LOAD DATA INFILE '{0}' INTO TABLE keytable  FIELDS TERMINATED BY '\t' (keypress,processname,dtstamp) set userid={1}",
                                            FileName, id);
                       break;
                    }
                case DataType.SysInfo:
                    {
                        CmdStr = string.Format(@"LOAD DATA INFILE '{0}' INTO TABLE sysinfotable  FIELDS TERMINATED BY '\t' (cpu,ram,diskwrite,diskread,dtstamp) set userid={1}",
                                            FileName, id);
                        break;
                    }
                default:
                    throw new System.Exception("Invalid Datatype");
            }
            MySqlCommand cmd = Connection.CreateCommand();
            cmd.CommandText = CmdStr;
            cmd.ExecuteNonQuery();
        }

        public DateTime GetUserLastUploadTime(int id, DataType type)
        {
            MySqlCommand Cmd = new MySqlCommand();
            string TableName;
            switch (type)
            {
                case DataType.Key:
                    {
                        TableName = "keytable";
                        break;
                    }
                case DataType.Mouse:
                    {
                        TableName = "mousetable";
                        break;
                    }
                case DataType.SysInfo:
                    {
                        TableName = "sysinfotable";
                        break;
                    }
                default:
                    throw new System.Exception("Invalid Datatype");
            }
            string CmdStr = string.Format(
                @"select max(dtstamp) as dt from {1} where userid={0}"
                , id,TableName);
            Cmd.CommandText = CmdStr;
            Cmd.Connection = Connection;
            var result = Cmd.ExecuteScalar();
            if (result != System.DBNull.Value)
            {
                return (DateTime)result;
            }
            return new DateTime(0);
        }

        private MySqlConnection Connection;

        public string GetPath(int uuid)
        {
            string cmd = @"select  pluginpath from plugininfo where uuid=" + uuid.ToString();
            var SQLCmd = this.Connection.CreateCommand();
            SQLCmd.CommandText=cmd;
            var result = SQLCmd.ExecuteScalar();
            if (result == null)
                return null;
            else
                return (string)result;
        }

        public string GetImagePath(int uuid)
        {
            string cmd = @"select  pluginimagepath from plugininfo where uuid=" + uuid.ToString();
            var SQLCmd = this.Connection.CreateCommand();
            SQLCmd.CommandText = cmd;
            var result = SQLCmd.ExecuteScalar();
            if (result == null)
                return null;
            else
                return (string)result;
        }

        public DataTable GetPluginInfo()
        {
            try
            {
                string cmd = @"select * from plugininfo";
                MySqlDataAdapter ad = new MySqlDataAdapter();
                ad.SelectCommand = new MySqlCommand(cmd, Connection);
                var dt = new DataTable();
                ad.FillSchema(dt, SchemaType.Mapped);
                ad.Fill(dt);
                return dt;
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(ex.Message);
                return null;
            }
            
        }
    }
}
